﻿using H2.NET.Common;
using H2.NET.DBUtility;
using H2.NET.Web.BI.Model;
using H2.NET.Web.Workflow.DwzCode;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace H2.NET.Web.BI.Template
{
    public partial class Excel : BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            int id = AksRequest.GetQueryInt("id");
            if (id > 0)
            {
                DataTable result = null;
                var context = new UltimusMDC3Entities();
                var template = context.bi_Template.Find(id);
                if (template == null)
                {
                    ErrorMsg("对不起，该模版不存在！");
                    return;
                }
                string sql = template.sql;
                string tsql = template.sql;
                string where = AksRequest.GetQueryString("where") ?? "";
                if (!string.IsNullOrEmpty(where))
                {
                    sql = sql.Replace(" where", " " + where + " and ").Replace(" nowhere", " " + where);
                    tsql = tsql.Replace(" where", " " + where + " and ").Replace(" nowhere", " " + where);
                }
                else
                {
                    sql = sql.Replace("nowhere", "");
                    tsql = tsql.Replace("nowhere", "");
                }
                string pageFooter = ") tt ";
                if (sql.IndexOf("union") >= 0)
                {
                    sql = sql.Replace("union", pageFooter + " union ");
                    DataTable tempData1 = DbHelperSQL.Query(sql.Substring(0, sql.IndexOf("union"))).Tables[0];
                    DataTable tempData2 = DbHelperSQL.Query(sql.Substring(sql.IndexOf("union") + 5)).Tables[0];
                    result = AppendDataTable(tempData1, tempData2);
                    tsql = tsql.Substring(0, tsql.IndexOf("union")) + " ) tt";
                }
                else
                {
                    sql = sql + pageFooter;
                    result = DbHelperSQL.Query(sql).Tables[0];
                }
                if (result != null && result.Rows.Count > 0)
                {
                    result.TableName = template.name;
                    var widthList = template.bi_TemplateDetail.Where(b => b.fix == false && b.filter == false).Select(b => b.width).ToList();
                    Response.Clear();
                    Response.ClearContent();
                    Response.Buffer = true;
                    Response.Charset = "utf-8";
                    Response.ContentEncoding = Encoding.UTF8;
                    Response.ContentType = "application/ms-excel";
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(result.TableName) + ".xls");
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = (ISheet)workbook.CreateSheet("Sheet1");
                    //表头
                    IRow header = sheet.CreateRow(0);
                    header.Height = 200 * 2;
                    int colIndex = 0;
                    List<string> titles = new List<string>();
                    foreach (DataColumn column in result.Columns)
                    {
                        string[] caption = column.Caption.Split('_');
                        ICell cell = header.CreateCell(colIndex);
                        if (caption.Length > 1)
                        {
                            titles.Add(caption[1]);
                            cell.SetCellValue(caption[1]);
                        }
                        else 
                        {
                            titles.Add(caption[0]);
                            cell.SetCellValue(caption[0]);
                        }
                        sheet.AutoSizeColumn(colIndex);
                        //var cwidth = template.bi_TemplateDetail.Where(t => t.bi_Column.name_cn == caption[0]).First().width;
                        //sheet.SetColumnWidth(colIndex, (int)((cwidth / 16) > 255 ? 255 : (cwidth / 16)) * 256); //列宽
                        colIndex++;
                    }
                    sheet.CreateFreezePane(0, 1); //冻结表头

                    //数据
                    int rowIndex = 1;
                    foreach (DataRow dr in result.Rows)
                    {
                        IRow row = sheet.CreateRow(rowIndex);
                        row.Height = 200 * 2;
                        colIndex = 0;
                        foreach (DataColumn column in result.Columns)
                        {
                            var value = dr[column.ColumnName];
                            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                            {
                                var title = titles[colIndex];
                                var type = template.bi_TemplateDetail.Where(t => t.bi_Column.name_cn == title).First().bi_Column.type;
                                ICell cell = row.CreateCell(colIndex);
                                switch (type)
                                {
                                    case "string":
                                    case "bit":
                                        cell.SetCellType(CellType.String);
                                        cell.SetCellValue(value.ToString());
                                        break;
                                    case "datetime":
                                        cell.SetCellType(CellType.String);
                                        cell.SetCellValue((DateTime.Parse(value.ToString())).ToString("yyyy-MM-dd"));
                                        break;
                                    case "int":
                                        cell.SetCellType(CellType.Numeric);
                                        cell.SetCellValue(int.Parse(value.ToString()));
                                        break;
                                    case "float":
                                        cell.SetCellType(CellType.Numeric);
                                        cell.SetCellValue(float.Parse(value.ToString()));
                                        break;
                                    default:
                                        break;
                                }
                            }
                            colIndex++;
                        }
                        rowIndex++;
                    }

                    using (var stream = new MemoryStream())
                    {
                        workbook.Write(stream);
                        Response.BinaryWrite(stream.ToArray());
                    }
                    Response.End();
                }
            }
            return;
        }

        public static DataTable AppendDataTable(DataTable hostDt, DataTable clientDt)
        {
            if (hostDt != null && hostDt.Rows.Count > 0)
            {
                object[] obj = new object[hostDt.Columns.Count];
                for (int i = 0; i < clientDt.Rows.Count; i++)
                {
                    clientDt.Rows[i].ItemArray.CopyTo(obj, 0);
                    hostDt.Rows.Add(obj);
                }
            }
            return hostDt;
        }

    }
}